<?php

namespace app\service;


use app\model\PlanModel;
use app\tool\SingletonTrait;

class PlanService
{
    use SingletonTrait;

    public function __construct()
    {
    }

    /**
     * 获取排期剧集列表
     * @param int $startTime
     * @param int $endTime
     * @param int $projectId
     * @param array $categoryIdList
     * @return array
     */
    public function getPlanStepCategoryEntityList(int $startTime, int $endTime, int $projectId, array $categoryIdList): array
    {
        $yearMonthEntityList = (new \app\model\PlanModel)
            ->field("from_unixtime(plan.end_time,'%Y%m') as year_month,from_unixtime(plan.end_time,'%Y') as year,from_unixtime(plan.end_time,'%m') as month,entity_id")
            ->join(sprintf("(select task_id from task_relation_tag where link_id in (%s) and link_module_code in ('show_step_category', 'step_category')) task_relation_tag", join(',', $categoryIdList)), 'task_relation_tag.task_id = plan.task_id')
            ->where('plan.scope', 'single')
            ->where('plan.type', 'step_category')
            ->whereRaw(sprintf("plan.start_time <= %d and plan.end_time >= %d and plan.project_id = %d", $endTime, $startTime, $projectId))
            ->group('year_month,year,month,entity_id')
            ->select();
        if (empty($yearMonthEntityList->toArray())) {
            return [];
        }
        return $yearMonthEntityList->toArray();
    }

    /**
     * 获取指定用户剧集列表
     * @param $userIds
     * @param $projectId
     * @param $startTime
     * @param $endTime
     * @return array
     */
    public function getUserEpisodeEntityList($userIds, $projectId, $startTime, $endTime): array
    {
        $yearMonthEntityList = PlanModel::field("from_unixtime(plan.end_time,'%Y%m') as year_month,from_unixtime(plan.end_time,'%Y') as year,from_unixtime(plan.end_time,'%m') as month,entity_id")
            ->join('task_member', "plan.task_id = task_member.task_id")
            ->join(sprintf("(select link_id,task_id from task_relation_tag where project_id=%d and link_module_code in ('episode')) r_episode", $projectId), 'plan.task_id = r_episode.task_id')
            ->whereRaw(sprintf("plan.project_id = %d and plan.type in ('meeting','review') and plan.end_time >= %d and plan.end_time <=%d", $projectId, $startTime, $endTime))
            ->whereIn('task_member.partner', join(',', $userIds))
            ->select();
        if (empty($yearMonthEntityList->toArray())) {
            return [];
        }
        return $yearMonthEntityList->toArray();
    }


    /**
     * 按年月统计排期任务数量
     * @param $projectId
     * @param $stepCategoryId
     * @param $episodeId
     * @param $startTime
     * @param $endTime
     * @return array
     */
    public function totalYearMonthTaskNumCount($projectId, $stepCategoryId, $episodeId, $startTime, $endTime): array
    {
        $prStepCategory = sprintf("(select task_id from task_relation_tag where project_id=%d and link_module_code in ('show_step_category', 'step_category') and link_id in (%s)) as pr_step_category", $projectId, join(',', $stepCategoryId));
        $trEpisode = sprintf("(select link_id,task_id from task_relation_tag where project_id=%d and link_module_code in ('episode') and link_id in (%s)) as tr_episode", $projectId, join(',', $episodeId));
        $trStepCategory = sprintf("(select task_id from task_relation_tag where project_id=%d and link_module_code in ('show_step_category', 'step_category')  and link_id in (%s)) as tr_step_category", $projectId, join(',', $stepCategoryId));
        $res = (new \app\model\PlanModel)
            ->field("from_unixtime(plan.end_time,'%Y%m') AS year_month,from_unixtime(plan.end_time,'%Y') as year,from_unixtime(plan.end_time,'%m') as month, count(distinct tr_step_category.task_id) as count")
            ->join($prStepCategory, 'plan.task_id = pr_step_category.task_id')
            ->join($trEpisode, 'plan.entity_id = tr_episode.link_id')
            ->join($trStepCategory, 'tr_episode.task_id = tr_step_category.task_id')
            ->whereRaw(sprintf("plan.scope = 'single' and plan.type = 'step_category' and plan.project_id = %d and plan.start_time <= %d and plan.end_time>= %d", $projectId, $endTime, $startTime))
            ->group('year_month,year,month')
            ->select();
        if (empty($res->toArray())) {
            return [];
        }
        return $res->toArray();
    }

    /**
     *查询集环节排期
     * @param array $projectIds
     * @param array $stepCategoryIds
     * @param int $startTime
     * @param int $endTime
     * @return array
     */
    public function getPlanEpisodeData(array $projectIds, array $stepCategoryIds, int $startTime, int $endTime): array
    {
        $dbr = PlanModel::field('p.entity_id episode_id,r_step_category.link_id as step_category_id,p.end_time,p.start_time');
        $dbr->alias('p');
        $dbr->join('task_relation_tag r_step_category', 'p.task_id = r_step_category.task_id');
        $dbr->whereIn('p.project_id', $projectIds);
        $dbr->where('p.type', 'step_category');
        $dbr->where('p.scope', 'single');
        $dbr->whereBetween('p.end_time', [$startTime, $endTime]);
        $dbr->whereIn('r_step_category.link_module_code', ['step_category', 'show_step_category']);
        $dbr->whereIn('r_step_category.link_id', $stepCategoryIds);
        $dbr = $dbr->select();
        if (empty($dbr->toArray())) {
            return [];
        }
        return $dbr->toArray();
    }

}